package Util;

import java.io.File;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.faces.context.FacesContext;
import javax.persistence.criteria.CriteriaBuilder.In;
import javax.servlet.ServletContext;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.mapping.Bag;


import Model.ChiTietHoaDon;
import Model.HoaDon;
import Model.NguoiDung;
import Model.NhaSanXuat;
import Model.SanPham;


public class DAO {

	public static int kiemtranguoidung(String ten, String matkhau){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
			Query query = session.createSQLQuery("select quyen from nguoidung where tennguoidung = ? and matkhau = ?");
			query.setString(0, ten);
			query.setString(1, matkhau);
			List<Integer> quyen = query.list();
			tran.commit();
			session.flush();
			session.close();
			if(quyen.isEmpty()){
				return 0;
			}
			return quyen.get(0).intValue();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}
	public static int luusanpham(SanPham sanpham){
		Session session = HibernateUtil.getSessionFactory().openSession();
		int kq = -1;
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
			session.save(sanpham);
			kq = 1;
			tran.commit();
			session.flush();
			session.close();
			return kq;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static SanPham kiemtrasanpham(String tensanpham){
		Session session = HibernateUtil.getSessionFactory().openSession();
		SanPham kq = null;
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
//			SQLQuery sq = session.createSQLQuery("select * from sanpham where ten ="+"'"+giatri+"';");
//			List<SanPham> l = (List<SanPham>)session.createQuery("from sanpham as s where s.ten = "+ "'"+giatri+"'").list();
			
//			System.out.println(l.size());
			kq = (SanPham) session.get(SanPham.class, tensanpham);
			
//			Query q = session.createQuery(
//				    "from SanPham as sp where sp.ten = ?");
//			q.setString(0, cot);
//			q.setString(0, giatri);
//			List<SanPham> l = q.list();
//			if(l.size() >0){
//			kq = (SanPham) l.get(0);
//			}
			tran.commit();
			session.flush();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	
	public static int capnhat(SanPham sanpham) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		int kq = -1;
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
			session.update(sanpham);
			kq = 1;
			tran.commit();
			session.flush();
			session.close();
			return kq;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static void xoasanpham(SanPham sp){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
			session.delete(sp);
			tran.commit();
			session.flush();
			session.close();
			
			ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
        	String folder = ctx.getRealPath("/resources/images/hinhsanpham/");
        	
        	if(!sp.getTen().equals("hinhmacdinh.jpg")){
        	File f = new File(folder+"/"+sp.getTen()+".jpg");
        	f.delete();
        	}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static NhaSanXuat laynhasanxuat(Integer id){
		Session session = HibernateUtil.getSessionFactory().openSession();
		NhaSanXuat kq = null;
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
			
			kq = (NhaSanXuat) session.get(NhaSanXuat.class, id);
			
			tran.commit();
			session.flush();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static NhaSanXuat laynhasanxuat(String nsx){
		Session session = HibernateUtil.getSessionFactory().openSession();
		NhaSanXuat kq = null;
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
			List<NhaSanXuat> list =session.createQuery("from NhaSanXuat where tennhasanxuat = ?").setString(0, nsx).list();
			if(!list.isEmpty()){
			kq = list.get(0);
			}
			else{
				kq = new NhaSanXuat();
			SQLQuery sqlq = session.createSQLQuery("select count(idnhasanxuat) from nhasanxuat");
			List<BigInteger> list1 = sqlq.list();
			Integer id = list1.get(0).intValue();
//			System.out.println(id);
			kq.setIdnhasanxuat(id+1);
			kq.setTennhasanxuat(nsx);
			}
			tran.commit();
			session.flush();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static int luunhasanxuat(NhaSanXuat nsx){
		Session session = HibernateUtil.getSessionFactory().openSession();
		int kq = -1;
		
		try {
			Transaction tran = null;
			tran = session.beginTransaction();
			session.save(nsx);
			kq = 1;
			tran.commit();
			session.flush();
			session.close();
			return kq;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static List<NhaSanXuat> laytatcanhasanxuat(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List<NhaSanXuat> kq = null;
		Transaction tran = null;
		try {
			tran = session.beginTransaction();
			kq =session.createQuery("from NhaSanXuat").list();
			tran.commit();
			session.flush();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}

public static List<SanPham> timkiemtheoten(String ten){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where ten like ?").setString(0, ten).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheocpu(String cpu){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where cpu like ?").setString(0, cpu).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheoram(String ram){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where ram like ?").setString(0, ram).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheohdd(String hdd){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where hdd like ?").setString(0, hdd).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}
public static List<SanPham> timkiemtheomanhinh(String manhinh){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where manHinh like ?").setString(0, manhinh).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}
public static List<SanPham> timkiemtheodohoa(String dohoa){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where doHoa like ?").setString(0, dohoa).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheoodia(String odia){
	
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where oDia like ?").setString(0, odia).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheowifi(String wifi){
	
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where wifi like ?").setString(0, wifi).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheowebcam(String webcam){
	
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where webcam like ?").setString(0, webcam).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheopin(String pin){
	
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where pin like ?").setString(0, pin).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheohdh(String hdh){
	
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where hdh like ?").setString(0, hdh).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheogia(Integer min, Integer max){
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		Query q =session.createQuery("from SanPham where gia > ? and gia < ?");
		q.setInteger(0, min);
		q.setInteger(1, max);
		kq = q.list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheokhuyenmai(String km){
	
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where khuyenMai like ?").setString(0, km).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheobaohanh(String bh){
	
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham where baoHanh like ?").setString(0, bh).list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static List<SanPham> timkiemtheohang(String hang){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	Set<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		List<NhaSanXuat> lnsx =session.createQuery("from NhaSanXuat where tennhasanxuat = ?").setString(0, hang).list();
		kq = lnsx.get(0).getSanphams();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	List<SanPham> lkq = new ArrayList<SanPham>(kq);
	return lkq;
}

public static List<SanPham> laytatcasanpham(){
	
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<SanPham> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq =session.createQuery("from SanPham").list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}
public static List<HoaDon> laydanhsachhoadon(Integer idnguoidung){
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<NguoiDung> lkq = null;
	Transaction tran = null;
	List<HoaDon> kq = null;
	try {
		tran = session.beginTransaction();
		lkq =session.createQuery("from NguoiDung where idnguoidung = ?").setInteger(0, idnguoidung).list();
//		System.out.println("lkq: "+lkq.get(0).getTen()+", id: "+lkq.get(0).getIdnguoidung());
//		System.out.println("dshd: "+lkq.get(0).getDshd().size());
		kq = new ArrayList<HoaDon>(lkq.get(0).getDshd());
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}
public static HoaDon layhoadon(int mahoadon){
	Session session = HibernateUtil.getSessionFactory().openSession();
	HoaDon kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq = (HoaDon) session.createQuery("from HoaDon where mahoadon = ?").setInteger(0, mahoadon).list().get(0);
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}
public static void luuhoadon(HoaDon hoadon){
	Session session = HibernateUtil.getSessionFactory().openSession();
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		session.save(hoadon);
		tran.commit();
		tran = session.beginTransaction();
		List<NguoiDung> lnguoidung = session.createQuery("from NguoiDung where tennguoidung = ?").setString(0, hoadon.getNguoidung().getTen()).list();
		NguoiDung cuoi = lnguoidung.get(lnguoidung.size()-1);
		int idnguoidung = cuoi.getIdnguoidung();
		List<HoaDon> lhd = session.createQuery("from HoaDon where idnguoidung = ?").setInteger(0, idnguoidung).list();
		HoaDon hdc = lhd.get(lhd.size()-1);
		int mahoadon = hdc.getMahoadon();
		Set<SanPham> dssp = hoadon.getDssp();
		
		for (SanPham sanPham : dssp) {
			int thanhtien = sanPham.getSoLuong()*sanPham.getGia().intValue();
			
			SQLQuery sql = session.createSQLQuery("update chitiethoadon set thanhtien = ?, soluong = ? where mahoadon = ? and tensanpham = ?");
			sql.setBigInteger(0, BigInteger.valueOf(thanhtien));
			sql.setInteger(1, sanPham.getSoLuong());
			sql.setInteger(2, mahoadon);
			sql.setString(3, sanPham.getTen());
			sql.getQueryString();
			sql.executeUpdate();
		}
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
public static void luunguoidung(NguoiDung nguoidung){
	Session session = HibernateUtil.getSessionFactory().openSession();
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		session.save(nguoidung);
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
public static List<HoaDon> laydanhsachhoadon(){
	Session session = HibernateUtil.getSessionFactory().openSession();
	List<HoaDon> kq = null;
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		kq = session.createQuery("from HoaDon").list();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}
public static void capnhathoadon(HoaDon hd){
	Session session = HibernateUtil.getSessionFactory().openSession();
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		session.update(hd);
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
public static void xoahoadon(HoaDon hd){
	Session session = HibernateUtil.getSessionFactory().openSession();
	Transaction tran = null;
	try {
		tran = session.beginTransaction();
		Set<SanPham> ssp = hd.getDssp();
		for (SanPham sanPham : ssp) {
			SQLQuery sq = session.createSQLQuery("delete from chitiethoadon where tensanpham = ? and mahoadon = ?");
			sq.setString(0, sanPham.getTen());
			sq.setInteger(1, hd.getMahoadon());
			sq.executeUpdate();
		}
		tran.commit();
		
		tran = session.beginTransaction();
		hd.setDssp(null);
		session.delete(hd);
		tran.commit();
		
		tran = session.beginTransaction();
		NguoiDung ng = hd.getNguoidung();
		ng.setDshd(null);
		session.delete(ng);
		tran.commit();
		
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}

public static Integer soluong(String tensanpham, Integer mahoadon){
	Session session = HibernateUtil.getSessionFactory().openSession();
	Transaction tran = null;
	Integer kq = 0;
	try {
		tran = session.beginTransaction();
		Query sq = session.createQuery("from ChiTietHoaDon where mahoadon = ? and tensanpham = ?");
		sq.setInteger(0, mahoadon);
		sq.setString(1, tensanpham);
		List<ChiTietHoaDon> lcthd = sq.list();
		kq = lcthd.get(0).getSoluong();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static BigInteger thanhtien(String tensanpham, Integer mahoadon){
	Session session = HibernateUtil.getSessionFactory().openSession();
	Transaction tran = null;
	BigInteger kq = null;
	try {
		tran = session.beginTransaction();
		Query sq = session.createQuery("from ChiTietHoaDon where mahoadon = ? and tensanpham = ?");
		sq.setInteger(0, mahoadon);
		sq.setString(1, tensanpham);
		List<ChiTietHoaDon> lcthd = sq.list();
		kq = lcthd.get(0).getThanhtien();
		tran.commit();
		session.flush();
		session.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return kq;
}

public static void main(String[] args) {
	System.out.println(thanhtien("abc1", 21));
}

}
